package db;

import utils.Common;
import android.content.ContentResolver;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;
import android.provider.ContactsContract;
import android.provider.ContactsContract.CommonDataKinds.Phone;
import android.util.Log;
import android.widget.Toast;

import com.readystatesoftware.sqliteasset.SQLiteAssetHelper;

public class MyDatabase extends SQLiteAssetHelper {
	private static final int DATABASE_VERSION = 1;
	private Context context;
	private String sql;

	public MyDatabase(Context context) {
		super(context, Common.DATABASE_NAME, null, DATABASE_VERSION);
		this.context = context;
	}

	public void syncContact() {
		SQLiteDatabase db = getReadableDatabase();
		ContentResolver cr = context.getContentResolver();
		db.beginTransaction();

		sql = "INSERT"
				+ " OR"
				+ " REPLACE INTO friends (phone, name, seen_datetime, is_seen, seen_event)"
				+ " VALUES (?,?,(SELECT IFNULL(seen_datetime, CURRENT_TIMESTAMP) FROM friends WHERE phone=?),(SELECT is_seen FROM friends WHERE phone=?),(SELECT seen_event FROM friends WHERE phone=?))";
		SQLiteStatement stm = db.compileStatement(sql);

		String[] cProject = { ContactsContract.Contacts._ID, ContactsContract.Contacts.DISPLAY_NAME, ContactsContract.Contacts.HAS_PHONE_NUMBER };
		Cursor cur = cr.query(ContactsContract.Contacts.CONTENT_URI, cProject, null, null, null);
		String id, name, phone;
		String[] pProject = { Phone.NUMBER };

		try {
			if (cur.getCount() > 0) {
				while (cur.moveToNext()) {
					id = cur.getString(cur.getColumnIndex(ContactsContract.Contacts._ID));
					name = cur.getString(cur.getColumnIndex(ContactsContract.Contacts.DISPLAY_NAME));
					if (Integer.parseInt(cur.getString(cur.getColumnIndex(ContactsContract.Contacts.HAS_PHONE_NUMBER))) > 0) {
						// get the phone number
						Cursor pCur = cr.query(ContactsContract.CommonDataKinds.Phone.CONTENT_URI, pProject, ContactsContract.CommonDataKinds.Phone.CONTACT_ID
								+ " = ?", new String[] { id }, null);
						while (pCur.moveToNext()) {
							phone = Common.refinePhone(pCur.getString(pCur.getColumnIndex(ContactsContract.CommonDataKinds.Phone.NUMBER)));
							stm.bindString(1, phone);
							stm.bindString(2, name);
							stm.bindString(3, phone);
							stm.bindString(4, phone);
							stm.bindString(5, phone);
							stm.execute();
						}
						pCur.close();
					}
				}
			}
			db.setTransactionSuccessful();
		} catch (Exception e) {
			Log.e("Exception", e.getMessage());
		} finally {
			cur.close();
			db.endTransaction();
			db.close();
		}

		Toast.makeText(context, "Sync contact complete !", Toast.LENGTH_SHORT).show();
	}

	public Cursor getSadList() {
		SQLiteDatabase db = getReadableDatabase();
		// @f:off
		sql = "SELECT rowid _id,"
				+ " phone,"
				+ " name,"
				+ " ROUND(julianday('now')-julianday(seen_datetime),1) pass_day,"
				+ " seen_event" + " FROM FRIENDS"
				+ " WHERE is_seen =1 AND pass_day >= 7"
				+ " ORDER BY pass_day DESC, lower(name)";
		return db.rawQuery(sql, null);
		// @f:on
	}

	public Cursor getFunList() {
		SQLiteDatabase db = getReadableDatabase();
		// @f:off
		sql = "SELECT rowid _id,"
				+ " phone,"
				+ " name,"
				+ " ROUND(julianday('now')-julianday(seen_datetime),1) pass_day,"
				+ " seen_event" + " FROM FRIENDS"
				+ " WHERE is_seen =1 AND pass_day < 7"
				+ " ORDER BY pass_day DESC, lower(name)";
		return db.rawQuery(sql, null);
		// @f:on
	}

	public Cursor getColdList() {
		SQLiteDatabase db = getReadableDatabase();
		// @f:off
		sql = "SELECT rowid _id,"
				+ " phone,"
				+ " name,"
				+ " ROUND(julianday('now')-julianday(seen_datetime),1) pass_day,"
				+ " seen_event" 
				+ " FROM FRIENDS" 
				+ " WHERE is_seen is null or is_seen <> 1"
				+ " ORDER BY pass_day DESC, lower(name)";
		return db.rawQuery(sql, null);
		// @f:on
	}

	public void updateSeen(String phone, String seen_event) {
		SQLiteDatabase db = getReadableDatabase();
		try {
			String sql = "UPDATE friends" + " SET seen_datetime = CURRENT_TIMESTAMP, seen_event = '" + seen_event + "', is_seen = 1" + " WHERE phone='" + phone
					+ "'";
			db.execSQL(sql);
		} catch (Exception e) {
		} finally {
			db.close();
		}
	}
	
	public void removeSeen(String phone) {
		SQLiteDatabase db = getReadableDatabase();
		try {
			String sql = "UPDATE friends SET seen_datetime = CURRENT_TIMESTAMP, seen_event = 'removed', is_seen = NULL WHERE phone='" + phone + "'";
			db.execSQL(sql);
		} catch (Exception e) {
		} finally {
			db.close();
		}
	}
}